package org.ofbiz.sys;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javolution.util.FastList;
import javolution.util.FastMap;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.ofbiz.sys.utils.ApiResponse;
import org.ofbiz.sys.utils.ResponseUtils;
import org.ofbiz.base.util.Debug;
import org.ofbiz.base.util.UtilDateTime;
import org.ofbiz.base.util.UtilMisc;
import org.ofbiz.base.util.UtilValidate;
import org.ofbiz.service.GenericServiceException;
import org.ofbiz.service.LocalDispatcher;
import org.ofbiz.webapp.event.EventHandlerException;

public class ExeclEvents {
    static String module = ExeclEvents.class.getName();

    @SuppressWarnings({ "unchecked" })
    public String importExcel(HttpServletRequest request, HttpServletResponse response) throws EventHandlerException {
	LocalDispatcher dispatcher = (LocalDispatcher) request.getAttribute("dispatcher");
	String serviceName = (String) request.getAttribute("serviceName");
	Map<String, Object> serviceContext = (Map<String, Object>) request.getAttribute("serviceContext");
	// 执行导入方法
	InputStream is = null;
	Workbook wb = null;
	try {
	    String uploadFileFileName = (String) serviceContext.get("uploadFileFileName");
	    String nonceStr = (String) serviceContext.get("nonceStr");
	    if (UtilValidate.isNotEmpty(uploadFileFileName)) {
		if (UtilValidate.isEmpty(nonceStr)) {
		    nonceStr = Long.toString(Math.round(99999999L * Math.random()));
		}

	    }
	    Map<String, Object> fileMap = dispatcher.runSync("uploadedFile",
		    UtilMisc.toMap("dependId", nonceStr, "uploadedFile", serviceContext.get("uploadFile"),
			    "_uploadedFile_fileName", serviceContext.get("uploadFileFileName"),
			    "_uploadedFile_contentType", serviceContext.get("uploadFileContentType")));
	    String fileName = "/sys/images/upload/" + fileMap.get("imagePath");

	    if (UtilValidate.isEmpty(fileName)) {
		return AdminEvents.sendError(response, request, "请选择上传Excel文件。");
	    }
	    if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
		return AdminEvents.sendError(response, request, "上传的文件格式不正确: " + fileName);
	    }

	    String fileDir = "/hot-deploy/sys/webapp/";
	    String OFBIZ_HOME = System.getProperty("ofbiz.home");
	    is = new FileInputStream(OFBIZ_HOME + fileDir + fileName);
	    Debug.log(OFBIZ_HOME + fileDir + fileName);
	    if (UtilValidate.isEmpty(is)) {
		return AdminEvents.sendError(response, request, "文件中无数据");
	    }
	    wb = WorkbookFactory.create(is);
	    Sheet sheet = wb.getSheetAt(0);
	    int rowNum = sheet.getLastRowNum(); // 行数
	    int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells(); // 列数
	    List<Map<String, Object>> mapList = FastList.newInstance();
	    for (int i = 1; i <= rowNum; i++) {
		Row row = sheet.getRow(i);
		if (UtilValidate.isEmpty(row)) {
		    continue;
		}
		Map<String, Object> map = FastMap.newInstance();
		for (int j = 0; j <= coloumNum; j++) {
		    String obj = getCellStringValue(row, j);
		    map.put("" + j, obj);
		}
		mapList.add(map);
	    }

	    Map<String, Object> serviceResults = FastMap.newInstance();
	    Map<String, Object> newServiceContext = FastMap.newInstance();
	    newServiceContext.put("sheetList", mapList);
	    newServiceContext.put("userLogin", serviceContext.get("userLogin"));
	    serviceResults = dispatcher.runSync(serviceName, newServiceContext);
	    List<String> newMessageList = (List<String>) serviceResults.get("messageList");
	    if (UtilValidate.isNotEmpty(newMessageList)) {
		return AdminEvents.sendError(response, request, newMessageList.toString());
	    }
	} catch (EncryptedDocumentException e) {
	    e.printStackTrace();
	    return AdminEvents.sendError(response, request, e.getMessage());
	} catch (FileNotFoundException e) {
	    e.printStackTrace();
	    return AdminEvents.sendError(response, request, e.getMessage());
	} catch (InvalidFormatException e) {
	    e.printStackTrace();
	    return AdminEvents.sendError(response, request, e.getMessage());
	} catch (IOException e) {
	    e.printStackTrace();
	    return AdminEvents.sendError(response, request, e.getMessage());
	} catch (EventHandlerException e) {
	    e.printStackTrace();
	    return AdminEvents.sendError(response, request, e.getMessage());
	} catch (GenericServiceException e) {
	    e.printStackTrace();
	    return AdminEvents.sendError(response, request, e.getMessage());
	} finally {
	    if (is != null) {
		try {
		    is.close();
		} catch (Exception e) {
		    Debug.logError(e, module);
		    return AdminEvents.sendError(response, request, e.getMessage());
		}
	    }
	}
	String body = "\"\"";
	ApiResponse apiResponse = new ApiResponse(request, body, "导入成功", "\"0\"");
	ResponseUtils.renderApiJson(response, request, apiResponse);
	return "success";
    }

    private static String getCellStringValue(Row row, int col) {
	Cell cell = row.getCell(col);
	if (cell == null) {
	    return null;
	}
	if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
	    return cell.getRichStringCellValue().toString().trim();
	} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
	    DecimalFormat df = new DecimalFormat("#.######");
	    return df.format(cell.getNumericCellValue());
	} else {
	    if (HSSFDateUtil.isCellDateFormatted(cell)) {
		// 用于转化为日期格式
		Date d = cell.getDateCellValue();
		if (UtilValidate.isNotEmpty(d)) {
		    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		    return formater.format(d);
		} else {
		    return null;
		}
	    } else {
		cell.setCellType(Cell.CELL_TYPE_STRING);
		return cell.getRichStringCellValue().toString().trim();
	    }
	}
    }

    @SuppressWarnings("unchecked")
    public String exportExcel(HttpServletRequest request, HttpServletResponse response) throws EventHandlerException {
	LocalDispatcher dispatcher = (LocalDispatcher) request.getAttribute("dispatcher");
	String serviceName = (String) request.getAttribute("serviceName");
	List<Map<String, Object>> list = FastList.newInstance();
	Map<String, Object> keyMap = FastMap.newInstance();
	Map<String, Object> keyItemMap = FastMap.newInstance();
	String title = "数据";
	String[] headers = new String[] {};
	Map<String, Object> serviceContext = (Map<String, Object>) request.getAttribute("serviceContext");
	Map<String, Object> serviceResults = FastMap.newInstance();
	try {
	    serviceResults = dispatcher.runSync(serviceName, serviceContext);
	} catch (GenericServiceException e1) {
	    e1.printStackTrace();
	    return AdminEvents.sendError(response, request, e1.getMessage());
	}
	list = (List<Map<String, Object>>) serviceResults.get("list"); // 查询数据
	keyMap = (Map<String, Object>) serviceResults.get("keyMap"); // 父列表的字段位置
	keyItemMap = (Map<String, Object>) serviceResults.get("keyItemMap"); // 子列表的字段位置
	headers = String.valueOf(serviceResults.get("headers")).split(",");

	if (UtilValidate.isEmpty(list)) {
	    response.setContentType("text/html;charset=GBK");
	    try {
		response.getWriter().print("订单条件查询结果为空, 请调整查询条件再导出！");
	    } catch (IOException e) {
		e.getStackTrace();
	    }
	    return "error";
	}

	HSSFWorkbook workbook = new HSSFWorkbook();
	HSSFSheet sheet = workbook.createSheet(title);

	HSSFCellStyle style = workbook.createCellStyle();
	style.setFillForegroundColor(IndexedColors.WHITE.index);
	style.setFillPattern((short) 1);
	style.setBorderBottom((short) 1);
	style.setBorderLeft((short) 1);
	style.setBorderRight((short) 1);
	style.setBorderTop((short) 1);
	style.setAlignment((short) 2);
	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	HSSFFont font = workbook.createFont();
	font.setColor(IndexedColors.VIOLET.index);
	font.setFontHeightInPoints((short) 12);
	font.setBoldweight((short) 700);
	style.setFont(font);

	HSSFCellStyle style1 = workbook.createCellStyle();
	style1.setFillForegroundColor(IndexedColors.WHITE.index);
	style1.setFillPattern((short) 1);
	style1.setBorderBottom((short) 1);
	style1.setBorderLeft((short) 1);
	style1.setBorderRight((short) 1);
	style1.setBorderTop((short) 1);
	style1.setAlignment((short) 2);
	style1.setVerticalAlignment((short) 1);
	HSSFFont font1 = workbook.createFont();
	font1.setBoldweight((short) 400);
	style1.setFont(font1);

	HSSFCellStyle style2 = workbook.createCellStyle();
	style2.setFillForegroundColor(IndexedColors.WHITE.index);
	style2.setFillPattern((short) 1);
	style2.setBorderBottom((short) 1);
	style2.setBorderLeft((short) 1);
	style2.setBorderRight((short) 1);
	style2.setBorderTop((short) 1);
	style2.setAlignment((short) 2);
	style2.setVerticalAlignment((short) 1);
	HSSFFont font2 = workbook.createFont();
	font2.setBoldweight((short) 400);
	style2.setFont(font2);
	int rowNum = 0;
	int index = 0;
	// 表头
	HSSFRow row = sheet.createRow(rowNum++);
	row.setHeightInPoints(18);
	for (int i = 0; i < headers.length; i++) {
	    setCell(row, i, headers[i], style); // header
	}
	ServletOutputStream out = null;
	try {
	    for (Map<String, Object> newEntity : list) {
		int remainder = index++ % 2 + 1;
		HSSFCellStyle orderStyle = style2;
		List<Map<String, Object>> itemList = (List<Map<String, Object>>) newEntity.get("itemList");
		if (UtilValidate.isNotEmpty(keyItemMap)) {
		    for (int i = 0; i < itemList.size(); i++) {
			Map<String, Object> item = (Map<String, Object>) itemList.get(i);
			if (remainder == 1) {
			    orderStyle = style1;
			}
			HSSFRow itemRow = sheet.createRow(rowNum++);
			// 父子表
			if (i == 0) {
			    for (String key : keyMap.keySet()) {
				if (!key.equals("itemList")) {
				    setCell(itemRow, Integer.valueOf(key),
					    String.valueOf(newEntity.get(keyMap.get(key))), orderStyle);
				}
			    }
			} else {
			    setCell(itemRow, 0, String.valueOf(newEntity.get(keyMap.get("0"))), orderStyle);
			}
			for (String key : keyItemMap.keySet()) {
			    setCell(itemRow, Integer.valueOf(key), String.valueOf(item.get(keyItemMap.get(key))),
				    orderStyle);
			}
		    }
		} else {
		    if (remainder == 1) {
			orderStyle = style1;
		    }
		    HSSFRow itemRow = sheet.createRow(rowNum++);
		    // 单表
		    for (String key : keyMap.keySet()) {
			Object keyValue = keyMap.get(key);
			String value = "";
			if(UtilValidate.isNotEmpty(newEntity.get(keyValue))) {
			    value = String.valueOf(newEntity.get(keyValue));
			}
			
			setCell(itemRow, Integer.valueOf(key),value,orderStyle);
		    }
		}
	    }
	    // 设置文件输出属性
	    String fileName = "list[" + UtilDateTime.nowDateString() + "].xls";
	    setDownloadFileHearder(request, response, fileName);
	    // 写流文件到客户端
	    out = response.getOutputStream();
	    workbook.write(out);
	    out.close();
	    return "success";
	} catch (IOException e) {
	    Debug.logError(e, e.getMessage(), module);
	    return AdminEvents.sendError(response, request, e.getMessage());
	} finally {
	    if (out != null) {
		try {
		    out.close();
		} catch (Exception e) {
		    Debug.logError(e, e.getMessage(), module);
		    return AdminEvents.sendError(response, request, e.getMessage());
		}
	    }
	}
    }

    /**
     * 创建 单元格, 设置内容
     * 
     * @param r     行对象
     * @param col   单元格列 序号
     * @param s     单元格内容
     * @param style 单元格风格
     */
    public static void setCell(HSSFRow r, int col, String s, HSSFCellStyle style) {
	HSSFCell cell = r.createCell(col);
	if (style != null)
	    cell.setCellStyle(style);
	if (s != null) {
	    HSSFRichTextString text = new HSSFRichTextString(s);
	    cell.setCellValue(text);
	}
    }

    /**
     * 设置下载操作响应头配置 <br/>
     * <br/>
     * 兼容各浏览器的文件下载时中文名称乱码的解决方案
     * 
     * @param request
     * @param response
     * @param fileName 文件全名
     */
    public static void setDownloadFileHearder(HttpServletRequest request, HttpServletResponse response,
	    String fileName) {
	String userAgent = request.getHeader("User-Agent");
	// 默认以 IE 浏览器配置获取文件名称
	byte[] bytes = fileName.getBytes();
	if (!userAgent.contains("MSIE")) {
	    try {
		bytes = fileName.getBytes("UTF-8");
	    } catch (UnsupportedEncodingException e) {
		Debug.logError(e, e.getMessage(), module);
	    }
	}
	try {
	    // 各浏览器基本都支持ISO编码
	    fileName = new String(bytes, "ISO-8859-1");
	} catch (UnsupportedEncodingException e) {
	    Debug.logError(e, e.getMessage(), module);
	}
	response.setContentType("application/vnd.ms-excel;charset=UTF-8");
	response.setCharacterEncoding("UTF-8");
	try {
	    response.setHeader("Content-Disposition",
		    "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
	} catch (UnsupportedEncodingException e) {
	    e.getMessage();
	}

    }
}
